﻿#region Copyright (C) Sector7 Software Studio. All rights reserved
/*******************************************************************************************
 * Creation
 *      Author:         Wolf Lai
 *      Date:           2013/6/6 星期四 23:37:46
 *      Version:        
 * Modification 
 *      Author: 
 *      Date: 
 *      Description: 
 *      Version:
 *******************************************************************************************/
#endregion
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Sector7.Medical.EntityType;
using Sector7.Framework.DB;
using Sector7.Medical.Consts;
using Sector7.Medical.DAO.Utility;
using Sector7.Medical.Enums;
using Sector7.Framework.Utility;
using Sector7.Framework.Core;
using Sector7.Framework.Enums;

namespace Sector7.Medical.DAO.Standard
{
    /// <summary>
    /// <!-- The description about this class. -->
    /// </summary>
    public class DrugDAO : DAOBase<M7_Drug>, IDrugDAO
    {
        #region Public functions

        public override object Create(M7_Drug drug)
        {
            using (DBHelper helper = new DBHelper())
            {
                var columns = new string[] {
                    C_ColumnName.DRUG_ID,
                    C_ColumnName.PHYSICAL_TYPE_ID,
                    C_ColumnName.NATURE_TYPE_ID,
                    C_ColumnName.DRUG_FORM_ID,
                    C_ColumnName.DRUG_NAME,
                    C_ColumnName.SPELL_CODE,
                    C_ColumnName.SPEC,
                    C_ColumnName.SPEC_UNIT_ID,
                    C_ColumnName.USAGE_UNIT_ID,
                    C_ColumnName.PRODUCT_NAME,
                    C_ColumnName.EXECUTIVE_STANDARD,
                    C_ColumnName.DRUG_REG_TYPE_ID,
                    C_ColumnName.DRUG_REG_NUMBER,
                    C_ColumnName.MANUFACTURER_ID,
                    C_ColumnName.IS_ON_SELL,
                    C_ColumnName.BAR_CODE};

                return helper.ExecuteScalar(
                    DBUtil.CreateInsertSql(C_M7_TableName.DRUG, false, columns),
                    DBUtil.CreateSqlParameterArray(drug, columns));
            }
        }

        public List<M7_Drug> ReadAll(int natureTypeId, DrugPropertyType properties)
        {
            List<M7_Drug> drugList = new List<M7_Drug>();

            string sql = GenerateSelectSql("d.NatureTypeId = @NatureTypeId", properties);

            using (DBHelper helper = new DBHelper())
            {
                SqlDataReader reader = helper.ExecuteQuery(sql,
                    DBUtil.CreateSqlParameter(natureTypeId, "NatureTypeId"));

                while (reader.Read())
                {
                    var drug = ResolveToDrug(reader, properties);
                    if (drugList.Find(delegate(M7_Drug __drug)
                    {
                        return __drug.DrugId == drug.DrugId;
                    }) == null)
                    {
                        drugList.Add(drug);
                    }
                }

                return drugList;
            }
        }

        public List<M7_Drug> ReadAll(string keywords, DrugPropertyType properties)
        {
            List<M7_Drug> drugList = new List<M7_Drug>();

            string sql = GenerateSelectSql("d.DrugId LIKE '%{0}%' OR d.DrugName LIKE '%{0}%' OR d.SpellCode LIKE '%{0}%'",
                properties);
            sql = string.Format(sql, keywords);

            using (DBHelper helper = new DBHelper())
            {
                SqlDataReader reader = helper.ExecuteQuery(sql);

                while (reader.Read())
                {
                    var drug = ResolveToDrug(reader, properties);

                    if ((properties & DrugPropertyType.SpecList) == DrugPropertyType.SpecList)
                    {
                        IDrugSpecDAO __specDAO = new DrugSpecDAO();
                        drug.SpecList = __specDAO.ReadAll(drug.DrugId);
                    }

                    drugList.Add(drug);
                }
            }

            return drugList;
        }

        public int Count(string drugName)
        {
            int count = 0;

            string sql = string.Format(@"
            SELECT
                COUNT({0}) AS count
            FROM
                M7_Drug
            WHERE
                {0} = @{0}", C_ColumnName.DRUG_NAME);

            using (DBHelper helper = new DBHelper())
            {
                SqlDataReader reader = helper.ExecuteQuery(sql,
                    DBUtil.CreateSqlParameter(drugName, C_ColumnName.DRUG_NAME));

                if (reader.Read())
                {
                    count = Convert.ToInt32(reader[0]);
                }
            }

            return count;
        }

        public List<M7_Drug> ReadDrugsWithPrice(string keywords)
        {
            var drugs = new List<M7_Drug>();

            string sql = string.Format(@"
            SELECT
                d.*,
                dsu.UnitId DSU_UnitId,
                dsu.UnitName DSU_UnitName,
                duu.UnitId DUU_UnitId,
                duu.UnitName DUU_UnitName,
                ds.SpecId DS_SpecId,
                ds.PackSpec DS_PackSpec,
                ds.StockSpec DS_StockSpec,
                dspu.UnitId DSPU_UnitId,
                dspu.UnitName DSPU_UnitName,
                dssu.UnitId DSSU_UnitId,
                dssu.UnitName DSSU_UnitName,
                p.PriceId P_PriceId,
                p.PurchasesPrice P_PurchasesPrice,
                p.SalesPrice P_SalesPrice,
                p.UnitId P_UnitId,
                p.SpecId P_SpecId,
                pu.UnitId PU_UnitId,
                pu.UnitName PU_UnitName
            FROM
                M7_DrugPrice p
                LEFT JOIN M7_Unit pu ON p.UnitId = pu.UnitId
                LEFT JOIN M7_Drug d ON d.DrugId = p.DrugId
                LEFT JOIN M7_Unit dsu ON d.SpecUnitId = dsu.UnitId
                LEFT JOIN M7_Unit duu ON d.UsageUnitId = duu.UnitId
                LEFT JOIN M7_DrugSpec ds ON p.SpecId = ds.SpecId
                LEFT JOIN M7_Unit dspu ON ds.PackUnitId = dspu.UnitId
                LEFT JOIN M7_Unit dssu ON ds.StockUnitId = dssu.UnitId
            WHERE
                d.DrugId LIKE '%{0}%' OR d.DrugName LIKE '%{0}%' OR d.SpellCode LIKE '%{0}%'",
                keywords);

            using (DBHelper helper = new DBHelper())
            {
                SqlDataReader reader = helper.ExecuteQuery(sql);

                while (reader.Read())
                {
                    var drug = ResolveToDrug(reader, DrugPropertyType.Price | DrugPropertyType.Spec);

                    drugs.Add(drug);
                }
            }

            return drugs;
        }

        #endregion

        #region Private functions

        private string GenerateSelectSql(string partForWhere, DrugPropertyType properties)
        {
            string sql = @"
            SELECT                
                d.*,
                pt.DrugTypeId PT_DrugTypeId,
                pt.DrugTypeName PT_DrugTypeName,
                nt.DrugTypeId NT_DrugTypeId,
                nt.DrugTypeName NT_DrugTypeName,
                df.Value DF_Value,
                df.Implication DF_Implication,
                df.Description DF_Description,
                dr.Value DR_Value,
                dr.Implication DR_Implication,
                dr.Description DR_Description,
                dsu.UnitId DSU_UnitId,
                dsu.UnitName DSU_UnitName,
                duu.UnitId DUU_UnitId,
                duu.UnitName DUU_UnitName                
            FROM                
                M7_Drug d
                LEFT JOIN M7_DrugType pt ON d.PhysicalTypeId = pt.DrugTypeId
                LEFT JOIN M7_DrugType nt ON d.NatureTypeId = nt.DrugTypeId
                LEFT JOIN M7_DrugForm df ON d.DrugFormId = df.Value
                LEFT JOIN M7_DrugRegType dr ON d.DrugRegTypeId = dr.Value
                LEFT JOIN M7_Unit dsu ON d.SpecUnitId = dsu.UnitId
                LEFT JOIN M7_Unit duu ON d.UsageUnitId = duu.UnitId                
            WHERE
                " + partForWhere;

            string partForSelect = string.Empty;
            string partForFrom = string.Empty;

            if ((properties & DrugPropertyType.Stock) == DrugPropertyType.Stock)
            {
                partForSelect += @",
                s.StockId S_StockId,
                s.BatchNumber S_BatchNumber,
                s.LotNumber S_LotNumber,
                s.Number S_Number,
                s.ProducedDate S_ProducedDate,
                s.AvailableDate S_AvailableDate,
                s.IsOverdue S_IsOverdue,
                su.UnitId SU_UnitId,
                su.UnitName SU_UnitName";

                partForFrom += @"
                LEFT JOIN M7_DrugStock s ON d.DrugId = s.DrugId
                LEFT JOIN M7_Unit su ON s.UnitId = su.UnitId";
            }
            if ((properties & DrugPropertyType.Frozen) == DrugPropertyType.Frozen)
            {
                partForSelect += @",
                f.FrozenId F_FrozenId,
                f.BatchNumber F_BatchNumber,
                f.LotNumber F_LotNumber,
                f.Number F_Number,
                fu.UnitId FU_UnitId,
                fu.UnitName FU_UnitName";

                partForFrom += @"
                LEFT JOIN M7_FrozenStock f ON d.DrugId = f.DrugId
                LEFT JOIN M7_Unit fu ON f.UnitId = fu.UnitId";
            }
            if ((properties & DrugPropertyType.Spec) == DrugPropertyType.Spec)
            {
                partForSelect += @",
                ds.SpecId DS_SpecId,
                ds.PackSpec DS_PackSpec,
                ds.StockSpec DS_StockSpec,
                dspu.UnitId DSPU_UnitId,
                dspu.UnitName DSPU_UnitName,
                dssu.UnitId DSSU_UnitId,
                dssu.UnitName DSSU_UnitName";

                partForFrom += @"
                LEFT JOIN M7_DrugSpec ds ON d.DrugId = ds.DrugId
                LEFT JOIN M7_Unit dspu ON ds.PackUnitId = dspu.UnitId
                LEFT JOIN M7_Unit dssu ON ds.StockUnitId = dssu.UnitId";
            }
            if ((properties & DrugPropertyType.Price) == DrugPropertyType.Price)
            {
                partForSelect += @",
                p.PriceId P_PriceId,
                p.PurchasesPrice P_PurchasesPrice,
                p.SalesPrice P_SalesPrice,
                p.UnitId P_UnitId,
                p.SpecId P_SpecId,
                pu.UnitId PU_UnitId,
                pu.UnitName PU_UnitName";

                partForFrom += @"
                LEFT JOIN M7_DrugPrice p ON ds.SpecId = p.SpecId
                LEFT JOIN M7_Unit pu ON p.UnitId = pu.UnitId";
            }

            if (properties != DrugPropertyType.Normal)
            {
                sql = string.Format(@"
                    SELECT
                        d.*{0}
                    FROM
                        ({1}) AS d
                        {2}",
                    partForSelect,
                    sql,
                    partForFrom);
            }

            return sql;
        }
        
        private M7_Drug ResolveToDrug(SqlDataReader reader, DrugPropertyType properties)
        {
            var drug = new M7_Drug();

            var physicalType = new M7_DrugType();
            var natureType = new M7_DrugType();
            var drugForm = new M7_DrugForm();
            var drugRegType = new M7_DrugRegType();
            var specUnit = new M7_Unit();
            var usageUnit = new M7_Unit();
            
            var drugStock = new M7_DrugStock();
            var stockUnit = new M7_Unit();
            
            var drugPrice = new M7_DrugPrice();
            var priceUnit = new M7_Unit();
            
            var frozenStock = new M7_FrozenStock();
            var frozenUnit = new M7_Unit();
            
            var drugSpec = new M7_DrugSpec();
            var specPackUnit = new M7_Unit();
            var specStockUnit = new M7_Unit();

            object[] values = new object[reader.FieldCount];
            reader.GetValues(values);
            for (int i = 0; i < reader.FieldCount; ++i)
            {
                string propertyName = reader.GetName(i);
                object propertyValue = values[i];

                FillEntityUtil.ToDrug(drug, propertyName, propertyValue);
                FillEntityUtil.ToDrugType(physicalType, "PT_", propertyName, propertyValue);
                FillEntityUtil.ToDrugType(natureType, "NT_", propertyName, propertyValue);
                FillEntityUtil.ToValueDomain(drugForm, "DF_", propertyName, propertyValue);
                FillEntityUtil.ToValueDomain(drugRegType, "DR_", propertyName, propertyValue);
                FillEntityUtil.ToUnit(specUnit, "DSU_", propertyName, propertyValue);
                FillEntityUtil.ToUnit(usageUnit, "DUU_", propertyName, propertyValue);
                
                FillEntityUtil.ToDrugStock(drugStock, "S_", propertyName, propertyValue);
                FillEntityUtil.ToUnit(stockUnit, "SU_", propertyName, propertyValue);
                
                FillEntityUtil.ToDrugPrice(drugPrice, "P_", propertyName, propertyValue);
                FillEntityUtil.ToUnit(priceUnit, "PU_", propertyName, propertyValue);
                
                FillEntityUtil.ToFrozenStock(frozenStock, "F_", propertyName, propertyValue);
                FillEntityUtil.ToUnit(frozenUnit, "FU_", propertyName, propertyValue);
                
                FillEntityUtil.ToDrugSpec(drugSpec, "DS_", propertyName, propertyValue);
                FillEntityUtil.ToUnit(specPackUnit, "DSPU_", propertyName, propertyValue);
                FillEntityUtil.ToUnit(specStockUnit, "DSSU_", propertyName, propertyValue);
            }

            drug.PhysicalType = physicalType;
            drug.NatureType = natureType;
            drug.DrugForm = drugForm;
            drug.DrugRegType = drugRegType;
            drug.SpecUnit = specUnit;
            drug.UsageUnit = usageUnit;

            if ((properties & DrugPropertyType.Stock) == DrugPropertyType.Stock)
            {
                drugStock.Unit = stockUnit;
                drug.Stock = drugStock;
            }
            if ((properties & DrugPropertyType.Price) == DrugPropertyType.Price)
            {
                drugPrice.Unit = priceUnit;
                drugPrice.Spec = drugSpec;
            }
            if ((properties & DrugPropertyType.Frozen) == DrugPropertyType.Frozen)
            {
                frozenStock.Unit = frozenUnit;
                drug.Frozen = frozenStock;
            }
            if ((properties & DrugPropertyType.Spec) == DrugPropertyType.Spec)
            {
                drugSpec.UsageUnit = usageUnit;
                drugSpec.PackUnit = specPackUnit;
                drugSpec.StockUnit = specStockUnit;
                if (drugSpec.SpecId > 0)
                {
                    if ((properties & DrugPropertyType.Price) == DrugPropertyType.Price)
                    {
                        drugSpec.Price = drugPrice;
                    }
                }
            }

            return drug;
        }

        #endregion

        #region Structure

        public DrugDAO()
        {
            m_tableName = C_M7_TableName.DRUG;
            m_primaryKeyName = C_ColumnName.DRUG_ID;
        }

        #endregion

        #region Inner members
        #endregion
    }
}
